
const Parse = require('parse/node');
const _=require('lodash');
const ParseHelper = require('../helpers/parse');
const ExcelService = require("./excel");
const KindObj = Parse.Object.extend("AutoBorrow_Kinds");
const BorrowListObj=Parse.Object.extend("AutoBorrow_BorrowList");
const FaultRecordObj=Parse.Object.extend("AutoBorrow_FaultRecord");
const KouDaiRecordObj=Parse.Object.extend("AutoBorrow_KouDaiRecord");
const RecordObj=Parse.Object.extend("AutoBorrow_Records");
const CabinetGridObj=Parse.Object.extend("AutoBorrow_CabinetGrids");
const CabinetObj=Parse.Object.extend("AutoBorrow_Cabinets");
const GoodsService =require("./goods")
const UserService=require('./user');
const BorrowTimeService=require('./borrow_time');
const WhiteListService =require('./white_list');
const CategoryObj = Parse.Object.extend("AutoBorrow_Category");
const GridObj = Parse.Object.extend("AutoBorrow_CabinetGrids");
const ExcelJS = require('exceljs');
const fs = require('fs');
const config=require("../config/config");
const path=require("path");
const moment = require("moment")
async function update(key,value,id){
    const Kind = KindObj.createWithoutData(id);
    Kind.set(key,value);
    return Kind.save(null,{ useMasterKey: true });
}


async function checkExcelFile(filePath,nameRowNumber,firstDataRowNumber,fieldsMapping){
    const kindsData=await ExcelService.parseExcelFileFirstSheet(filePath,nameRowNumber,firstDataRowNumber);
    if(kindsData.length>0){
        //取得前5行作为样例数据
        const exampleRows=_.take(kindsData,5);
        var mappedExampleRows=exampleRows.map(row=>mappingKindData(row,fieldsMapping));
        const result={
            exampleRows,
            mappedExampleRows,
            filePath,
            count:kindsData.length
        };
        return result;
    }else{
        return {error:'文件内容为空'};
    }
}

//根据fieldsMapping修改kindData中的key
function mappingKindData(kindData, fieldsMapping){
    const mappedKindData={...kindData};//复制要处理的数据
    Object.keys(mappedKindData).forEach(function(key) {
        const dbFieldName=fieldsMapping[key];
        if(dbFieldName){
            mappedKindData[dbFieldName] = mappedKindData[key];
            delete mappedKindData[key];
        }
    });
    return mappedKindData;
}

async function importExcelFile(filePath,nameRowNumber,firstDataRowNumber,fieldsMapping){
    const kindsData=await ExcelService.parseExcelFileFirstSheet(filePath,nameRowNumber,firstDataRowNumber);
    var mappedKindsData=kindsData.map(ud=>mappingKindData(ud,fieldsMapping));
    return _importKindData(mappedKindsData);
}

async function _importKindData(kindsData){
    const unsavedKinds=kindsData.map(kind=>{
        kind.count = Number(kind.count) || 0;
        kind.price = Number(kind.price) || 0;
        kind.used = 0;
        kind.hasCode = !!kind.hasCode;
        const parseKind = new KindObj(kind);
        return parseKind;
    });
    await Parse.Object.saveAll(unsavedKinds,{ useMasterKey: true });
    return  {regKindsCount:kindsData.length}
}


async function add(kind){
    kind.hasCode=kind.hasCode === true;
    kind.count = Number(kind.count) || 0;
    kind.price = Number(kind.price) || 0;
    kind.used = 0;
    const parseKind=new KindObj(kind);
    await parseKind.save(null,{ useMasterKey: true });
    return ParseHelper.toJSON(parseKind);
}
async function remove(ids){
    const unsavedKinds=ids.map(id=>{
        const parseKind =  KindObj.createWithoutData(id);
        parseKind.set("isDeleted",true)
        return parseKind;
    });
    return await Parse.Object.saveAll(unsavedKinds,{ useMasterKey: true });
}

async function save_borrow_list(list,user,isApply,time,hasUsed,scheduleId,whiteListId,count){
    const parseList = new BorrowListObj();
    parseList.set('list', list);
    parseList.set('user', user);
    parseList.set('isApply', isApply);
    parseList.set('hasUsed', hasUsed);
    // parseList.set('time', time);
    // parseList.set('scheduleId', scheduleId);
    parseList.set('whiteListId', whiteListId);
    // if(isApply){
    //     parseList.set('apply_time', moment().format('YYYY-MM-DD'));
    // }
    return parseList.save(null,{useMasterKey: true });
}


async function change_used_count(list,type){
    const group = _.groupBy(list, (item) => item.kind._id||item.kind.objectId);
    const kindIds = Object.keys(group);
    const unsavedKinds = await Promise.all(kindIds.map(async id => {
        // 同种类物品数量之和
        const kindCount = _.sum(group[id].map(item => item.count));
        const query = new Parse.Query(KindObj);
        query.notEqualTo('isDeleted', true);
        const parseKind = await query.get(id,{useMasterKey: true });
        const used = parseKind.get("used") || 0;
        const count=type==="add" ? Number(used) + Number(kindCount) : Number(used) - Number(kindCount)
        parseKind.set('used',count);
        return parseKind;
    }));
    return Parse.Object.saveAll(unsavedKinds,{ useMasterKey: true });
}


async function change_kind_used_count(kind,count,type){
    const query = new Parse.Query(KindObj);
    query.notEqualTo('isDeleted', true);
    const parseKind = await query.get(kind,{useMasterKey: true });
    const used = parseKind.get("used") || 0;
    const _count=type==="add" ? used + count : used - count;
    parseKind.set('used', Number(_count));
    return parseKind.save(null,{useMasterKey: true });
}
async function change_store_count(kindId,count){
    const query = new Parse.Query(KindObj);
    query.notEqualTo('isDeleted', true);
    const parseKind = await query.get(kindId,{useMasterKey: true });
    parseKind.increment('used', Number(count));
    return parseKind.save(null,{useMasterKey: true });
}


async function save_list(listId,newList){
    const parseList = BorrowListObj.createWithoutData(listId);
    parseList.set('list', newList);
    return parseList.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON);
}
async function change_status(listId, value,key){
    const parseList = BorrowListObj.createWithoutData(listId);
    parseList.set(key, value);
    return parseList.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON);
}

async function changeListStatus(listId,list){
    const parseList = BorrowListObj.createWithoutData(listId);
    parseList.set('list', list);
    parseList.set('hasUsed', true);
    // parseList.set('apply_time', new Date());
    return parseList.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON);
}

// async function changeListCount(listId, count){
//     const parseList = BorrowListObj.createWithoutData(listId);
//     parseList.set('count', count);
//     parseList.set('apply_time', new Date());
//     return parseList.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON);
// }

async function getWechatKindsByCategory(category){
    const query = new Parse.Query(KindObj);
    query.notEqualTo('isDeleted', true);
    query.equalTo('category', category);
    query.ascending("name");
    query.limit(999);
    return query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
}

async function getCategoryAndKinds() {
    const categoryQuery = new Parse.Query(CategoryObj);
    categoryQuery.notEqualTo('isDeleted', true);
    categoryQuery.ascending("name");
    const categories = await categoryQuery.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
    const categoryNames = categories.map(c => c.category);
    const kindsQuery = new Parse.Query(KindObj);
    kindsQuery.notEqualTo('isDeleted', true);
    kindsQuery.containedIn('category', categoryNames);
    kindsQuery.ascending("name");
    const kinds = await kindsQuery.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
    const kindsByCategory = _.groupBy(kinds, 'category');
    return categories.map(category =>{
        const kinds = kindsByCategory[category.category] || [];
        const children = kinds.map(kind => ({
            value: kind.objectId,
            label: kind.name
        }));
        return {
            value: category.objectId,
            label: category.category,
            children
        };
    });

    
}



async function getKindsByCategory(category) {
    const gridQuery = new Parse.Query(GridObj);
    gridQuery.exists("kindId");
    gridQuery.notEqualTo("isDeleted", true);

    const kindQuery = new Parse.Query(KindObj);
    kindQuery.notEqualTo('isDeleted', true);
    kindQuery.equalTo('category', category);
    kindQuery.ascending("name");
    kindQuery.limit(999);
  
    // 使用子查询过滤掉在 garidObj 中不存在的 KindObj
    kindQuery.matchesKeyInQuery("objectId", "kindId", gridQuery);

    return kindQuery.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
}

async function getKinds(){
    const query = new Parse.Query(KindObj);
    query.notEqualTo('isDeleted', true);
    query.ascending("name");
    query.limit(999);
    return query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
}

async function addToList(username, kind, count, time, isApply, scheduleId, whiteListId) {
    console.log(kind);
    const CabinetGridQuery = new Parse.Query(CabinetGridObj);
    let cabinetNames = [];
    CabinetGridQuery.equalTo("kindId", kind.objectId);
    const results = await CabinetGridQuery.find();
    if (results.length > 0) {
        // 提取并去重 cabinetId
        const cabinetIds = [...new Set(results.map(item => item.get("cabinetId")))];
        // 根据去重后的 cabinetId 列表查询 Cabinet 表
        const CabinetQuery = new Parse.Query(CabinetObj);
        CabinetQuery.containedIn("HID", cabinetIds);
        const cabinets = await CabinetQuery.find();
        // 整合所有查询到的 cabinet 的 name 字段
        cabinetNames = cabinets.map(cabinet => cabinet.get("name"));
    } else {
        console.log("未找到与该 kindId 匹配的网格记录");
    }
    const list = [
        {
            kind,
            count,
            cabinetNames,
        },
    ];

    // 等待异步数据准备完成后再调用保存方法
    await save_borrow_list(list, username, isApply, time, false, scheduleId, whiteListId);

    // 检查是否需要更新库存
    // if (kind.hasCode) {
    //     await change_kind_used_count(kind.objectId, Number(count), 'add');
    // } else {
    //     await GoodsService.change_good_owner_count(kind, Number(count), username, 'add');
    // }

    await BorrowTimeService.addUser(scheduleId, username);
    return kind;
}


async function getUserBorrowList(user){
    const query = new Parse.Query(BorrowListObj);
    query.notEqualTo('isDeleted', true);
    query.equalTo('user', user);
    // query.notEqualTo('hasReturn', true);
    query.ascending('createdAt');
    query.limit(9999);
    return query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
}

async function getWhiteListBorrowList(whiteListId){
    const query = new Parse.Query(BorrowListObj);
    query.notEqualTo('isDeleted', true);
    query.equalTo('whiteListId', whiteListId);
    query.limit(9999);
    return query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
}

async function getBorrowListById(id){
    const query = new Parse.Query(BorrowListObj);
    return query.get(id,{ useMasterKey: true }).then(ParseHelper.toJSON);
}

async function  getBorrowListByIds(ids) {
    const query = new Parse.Query(BorrowListObj);
    query.containedIn("objectId",ids);
    return query.find({ useMasterKey: true }).then(ParseHelper.toJSON)
}

async function getBorrowList(){
    const query = new Parse.Query(BorrowListObj);
    query.notEqualTo('isDeleted', true);
    query.notEqualTo('hasReturn', true);
    query.ascending('createdAt');
    query.limit(9999);
    const list=await query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
    const usernames=list.map(l=>l.user);
    const _users=await UserService.getUsersByUsernames(usernames);
    const users=ParseHelper.toJSONList(_users)
    const userDict=_.keyBy(users,'username');
    return list.map(l=>{
        const user=userDict[l.user]
        return{
            userInfo:user,
            ...l,
        }
    })
}

function getApplyCount(scheduleId){
    const query = new Parse.Query(BorrowListObj);
    query.notEqualTo('isDeleted', true);
    query.equalTo('scheduleId', scheduleId);
    query.limit(9999);
    return query.count({ useMasterKey: true });
}

async function getKindsByIds(ids){
    const query = new Parse.Query(KindObj);
    query.notEqualTo('isDeleted', true);
    query.containedIn('objectId', ids);
    query.limit(999);
    return query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
}

async function getUsersReturnedBorrowList(usernames,kindId){
    const query = new Parse.Query(BorrowListObj);
    query.notEqualTo('isDeleted', true);
    query.equalTo('hasReturn', true);
    query.containedIn('user', usernames);
    query.limit(9999);
    const borrow_list=await query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
    const _kinds=borrow_list.map(l=>l.list);
    const kinds=_.flatten(_kinds);
    const n_kind=kinds.filter(k=>k.kind.objectId||k.kind._id===kindId);
    return n_kind.length
}

async function getUsersApplyCount(usernames,kindId){
    const query = new Parse.Query(BorrowListObj);
    query.notEqualTo('isDeleted', true);
    query.equalTo('hasUsed', true);
    query.containedIn('user', usernames);
    query.limit(9999);
    const borrow_list=await query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
    const n_r=borrow_list.map(l=>{
        const kinds=l.list;
        const n_kind=kinds.filter(k=>k.kind.objectId||k.kind._id===kindId);
        return {n_kind,user:l.user}
    })
    const d_r=_.uniqBy(n_r,'user');
    const r=d_r.filter()
}
function getKindById(id){
    const query = new Parse.Query(KindObj);
    return  query.get(id,{ useMasterKey: true }).then(ParseHelper.toJSON);
}

async function users_borrow_list(usernames,kindId){
    const _users=await UserService.getUsersByUsernames(usernames);
    const users=ParseHelper.toJSONList(_users)
    const objs=usernames.map(username=>{
        const query = new Parse.Query(BorrowListObj);
        query.notEqualTo('isDeleted', true);
        query.equalTo('user', username);
        query.equalTo('hasUsed', true);
        query.descending('createdAt');
        return query.first({ useMasterKey: true }).then(ParseHelper.toJSON)
    });
    const _borrow_list=await Promise.all(objs);
    const borrow_list=_borrow_list.filter(function (item) {
        return item !== null && typeof item !== "undefined" && item !== "";
    });
    let result=[]
     await borrow_list.map(l=>{
        const kinds=l.list;
        const kind=kinds.find(k=>k.kind.objectId===kindId||k.kind._id===kindId);
        if(kind){
            result.push(l)
        }
    });
    return usernames.map(user=>{
        const existUser=users.find(u=>u.username===user);
        const borrowInfo=result.find(b=>b.user===user)||{};
        const status=borrowInfo.hasReturn?"已归还":borrowInfo.hasUsed?"已领用":"未领用";
        const goods=borrowInfo.list&&borrowInfo.list[0].goods||[];
        const goodId=borrowInfo.list&&borrowInfo.list[0].goodId;
        const good=goods.find(g=>g._id===goodId);

        return{...existUser,username:user,isExist:!!existUser,status,barcode:good&&good.barcode}
    })

}

async function exportExcel(isClosed){
    const whiteList=await WhiteListService.getAllWhiteListInfo(isClosed);
    const data=whiteList.map(async wl=>{
        const users=wl.usernames;
        const kindId=wl.kindId;
        const borrow_list=await users_borrow_list(users,kindId);
        return {name:wl.name,infos:borrow_list}
    });
    const _data=await Promise.all(data);
    //根据数据生成表格存到本地并返回路径
    const workbook = new ExcelJS.Workbook();
    _data.forEach(d=>{
        const worksheet = workbook.addWorksheet(d.name);
        worksheet.columns = [
            { header: '用户名', key: 'username' },
            { header: '姓名', key: 'showName' },
            { header: '状态', key: 'status' },
            { header: '资产编号', key: 'barcode' },
        ];
        d.infos.forEach(item=>{
            worksheet.addRow(item);
        })
    })
    const fileName=`${Date.now()}.xlsx`
    const dir=path.join(config.tmp_excel_dir,fileName);
    await  workbook.xlsx.writeFile(dir)
    return dir
}

async function add_category(category){
    const parseCategory = new CategoryObj(category);
    return parseCategory.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON);
}

async function getCategorys(){
    const query = new Parse.Query(CategoryObj);
    query.notEqualTo('isDeleted', true);
    query.ascending("name");
    query.limit(999);
    return query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
}

async function remove_category(id){
    const parseCategory = CategoryObj.createWithoutData(id);
    parseCategory.set("isDeleted",true)
    return parseCategory.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON);
}
    
async  function addFaultRecord(listId,scheduleId,username,text){
    const FaultRecord=new FaultRecordObj();
    FaultRecord.set('listId',listId)
    FaultRecord.set('scheduleId',scheduleId)
    FaultRecord.set('username',username);
    FaultRecord.set("text",text)
    FaultRecord.set('passed',false)
    return FaultRecord.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON)
}


async function getFaultRecords(listId){
    const query = new Parse.Query(FaultRecordObj);
    query.equalTo("listId",listId);
    query.doesNotExist("status");
    const records= await query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
    const scheduleIds=records.map(r=>r.scheduleId);
    const schedules=await BorrowTimeService.getSchedulesByIds(scheduleIds);
    const scheduleDict=_.keyBy(schedules,"objectId")
    return records.map(r=>{
        const schedule=scheduleDict[r.scheduleId]||{};
        const time=moment(r.updatedAt).format('YYYY-MM-DD HH:mm');
        return {
            ...r,schedule,time,status:r.isDone?"已完成":"待处理"
        }
    })
}

async function getAllFaultRecords(){
    const query = new Parse.Query(FaultRecordObj);
    const records= await query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
    const listIds=records.map(r=>r.listId);
    const borrow_list=await getBorrowListByIds(listIds);
    const usernames=records.map(b=>b.username);
    const parseUsers=await UserService.getUsersByUsernames(usernames);
    const users=ParseHelper.toJSONList(parseUsers);
    return records.map(r=>{
        const borrow=borrow_list.find(b=>b.objectId===r.listId)||{};
        const list =borrow.list||[];
        const user = users.find(u=>u.username===r.username)||{};
        return {
            ...r,list,user
        }
    })
}

async function  update_fault_record(key,value,id) {
    const FaultRecord = FaultRecordObj.createWithoutData(id);
    FaultRecord.set(key,value);
    return FaultRecord.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON);
}

async function complete_fault(id,content,url,listId,username){
    let FaultRecord;
    if(!id){
        FaultRecord=new FaultRecordObj();
        FaultRecord.set("listId",listId);
        FaultRecord.set("username",username);
    }else {
        FaultRecord = FaultRecordObj.createWithoutData(id);
    }
    FaultRecord.set('content',content)
    FaultRecord.set('url',url)
    FaultRecord.set('isDone',true);
    return FaultRecord.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON)
}

async function save_koudai_record(kindId,goodId,username,type){
    if(type==='lend'){
        const KouDaiRecord=new KouDaiRecordObj();
        KouDaiRecord.set('kindId',kindId);
        KouDaiRecord.set('goodId',goodId);
        KouDaiRecord.set('username',username);
        KouDaiRecord.set('borrow_time',new Date());
        return KouDaiRecord.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON)
    }
    else {
        const query = new Parse.Query(KouDaiRecordObj);
        query.equalTo("kindId",kindId);
        query.equalTo("goodId",goodId);
        query.equalTo("username",username);
        query.notEqualTo("isReturn",true);
        const KouDaiRecord=await query.first({ useMasterKey: true });
        KouDaiRecord.set("isReturn",true)
        KouDaiRecord.set("return_time",new Date())
        return KouDaiRecord.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON)
    }

}

async  function download_box_records(begin,end){
    const query = new Parse.Query(KouDaiRecordObj);
    query.notEqualTo("isDeleted", true);
    query.greaterThanOrEqualTo('createdAt', moment(begin).startOf('d').toDate());
    query.lessThanOrEqualTo('createdAt', moment(end).endOf('d').toDate());
    query.limit(99999)
    const records=await query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
    const kindIds=records.map(r=>r.kindId);
    const goodIds=records.map(r=>r.goodId);
    const usernames=records.map(r=>r.username);
    const kinds=await getKindsByIds(kindIds);
    const goods=await GoodsService.getGoodsByIds(goodIds);
    const _users=await UserService.getUsersByUsernames(usernames);
    const users=ParseHelper.toJSONList(_users)
    const whiteLists=await WhiteListService.getListByUsers(usernames);
    const kindDict=_.keyBy(kinds,'objectId')
    const goodsDict=_.keyBy(goods,'objectId')
    const userDict=_.keyBy(users,'username');
    const data=records.map(record=>{
        const user=userDict[record.username]||{};
        const kind=kindDict[record.kindId]||{};
        const good=goodsDict[record.goodId]||{};
        const whiteList=whiteLists.find(l=>l.usernames.includes(record.username));
        return{
                        'barcode':good.barcode,
                        'clazz':whiteList&&whiteList.name,
                        'studentNumber':user.studentNumber||user.username,
                        'showName':user.showName,
                        'deviceId':good.deviceId,
                        'kindName':kind.name,
                        'isReturn':record.isReturn?"已归还":"已借用",
                        'borrow_time':record.borrow_time?moment(record.borrow_time.iso).format('YYYY-MM-DD HH:mm'):null,
                        'return_time':record.return_time?moment(record.return_time.iso).format('YYYY-MM-DD HH:mm'):null,
        }
    })
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Sheet1');
    worksheet.columns = [
        { header: '资产编号', key: 'barcode' },
        { header: '学号', key: 'studentNumber'},
        { header: '班级', key: 'clazz'},
        { header: '姓名', key: 'showName'},
        { header: '设备编号', key: 'deviceId'},
        { header: '设备型号', key: 'kindName'},
        { header: '借还状态', key: 'isReturn'},
        { header: '借用时间', key: 'borrow_time'},
        { header: '归还时间', key: 'return_time'},
    ];
    for (const item of data) {
        worksheet.addRow(item);
    }
    const fileName=`${Date.now()}.xlsx`
    const dir=path.join(config.uploads_dir,fileName);
    await  workbook.xlsx.writeFile(dir)
    return dir
}

async function download_records(begin,end){
    const query = new Parse.Query(RecordObj);
    query.notEqualTo("isDeleted", true);
    query.greaterThanOrEqualTo('createdAt', moment(begin).startOf('d').toDate());
    query.lessThanOrEqualTo('createdAt', moment(end).endOf('d').toDate());
    query.limit(99999)
    const records=await query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
    const kindIds=records.map(r=>r.kind);
    const goodIds=records.map(r=>r.good);
    const usernames=records.map(r=>r.user);
    const kinds=await getKindsByIds(kindIds);
    const goods=await GoodsService.getGoodsByIds(goodIds);
    const _users=await UserService.getUsersByUsernames(usernames);
    const users=ParseHelper.toJSONList(_users)
    const kindDict=_.keyBy(kinds,'objectId')
    const goodsDict=_.keyBy(goods,'objectId')
    const userDict=_.keyBy(users,'username');
    const data=records.map(record=>{
        const user=userDict[record.user]||{};
        const kind=kindDict[record.kind]||{};
        const good=goodsDict[record.good]||{};
        return{
            'time':moment(record.createdAt).format('YYYY-MM-DD HH:mm'),
            'barcode':good.barcode,
            'studentNumber':user.studentNumber||user.username,
            'showName':user.showName,
            'deviceId':good.deviceId,
            'kindName':kind.name,
            "category":kind.category,
            'type':record.type==='lend'?"借用":"归还",
            'count':record.count,
            'price':kind.price,
        }
    })
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Sheet1');
    worksheet.columns = [
        { header: '日期', key: 'time'},
        { header: '资产编号', key: 'barcode' },
        { header: '学号', key: 'studentNumber'},
        { header: '姓名', key: 'showName'},
        { header: '设备编号', key: 'deviceId'},
        { header: '设备型号', key: 'kindName'},
        { header: '类别', key: 'category'},
        { header: '类型', key: 'type'},
        { header: '数量', key: 'count'},
        { header: '价格', key: 'price'},
    ];
    for (const item of data) {
        worksheet.addRow(item);
    }
    const fileName=`${Date.now()}.xlsx`
    const dir=path.join(config.uploads_dir,fileName);
    await  workbook.xlsx.writeFile(dir)
    return dir
}
async function getBorrowListByIds(ids){
    const query = new Parse.Query(BorrowListObj);
    query.notEqualTo('isDeleted', true);
    query.containedIn('objectId',ids);
    query.limit(9999);
    return query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
}


async function download_repair_records(begin,end){
    const query = new Parse.Query(FaultRecordObj);
    query.notEqualTo("isDeleted", true);
    query.greaterThanOrEqualTo('updatedAt', moment(begin).startOf('d').toDate());
    query.lessThanOrEqualTo('updatedAt', moment(end).endOf('d').toDate());
    query.limit(99999)
    const records=await query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
    const listIds=records.map(r=>r.listId);
    const scheduleIds=records.map(r=>r.scheduleId);
    const usernames=records.map(r=>r.username);
    const list=await getBorrowListByIds(listIds);
    const schedules=await BorrowTimeService.getSchedulesByIds(scheduleIds);
    const _users=await UserService.getUsersByUsernames(usernames);
    const users=ParseHelper.toJSONList(_users)
    const listDict=_.keyBy(list,'objectId');
    const scheduleDict=_.keyBy(schedules,'objectId');
    const userDict=_.keyBy(users,'username');
    const data=records.map(r=>{
        const listInfo=listDict[r.listId]||{};
        const schedule=scheduleDict[r.scheduleId]||{};
        const user=userDict[r.username]||{};
        const kinds=listInfo.list||[];
        const kindNames=kinds.map(k=>k.kind.name)||[];
        const goods=kinds.map(k=>k.goods[0].barcode)
        return {
            kinds:kindNames.join(','),
            goods:goods.join(','),
            schedule:schedule.date+" "+schedule.begin+"~"+schedule.end,
            studentNumber:user.studentNumber||user.username,
            updatedAt:moment(r.updatedAt).format('YYYY-MM-DD HH:mm'),
            showName:user.showName,
            isDone:r.isDone?"已处理":"未处理",
            content:r.content
        }
    })
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Sheet1');
    worksheet.columns = [
        { header: '资产', key: 'kinds'},
        { header: '资产编号', key: 'goods' },
        { header: '学号', key: 'studentNumber'},
        { header: '姓名', key: 'showName'},
        { header: '状态', key: 'isDone'},
        { header: '预约时间', key: 'schedule'},
        { header: '处理时间', key: 'updatedAt'},
        { header: '内容', key: 'content'},
    ];
    for (const item of data) {
        worksheet.addRow(item);
    }
    const fileName=`${Date.now()}.xlsx`
    const dir=path.join(config.uploads_dir,fileName);
    await  workbook.xlsx.writeFile(dir)
    return dir
}
async function getCategorys(){
    const query = new Parse.Query(CategoryObj);
    query.notEqualTo('isDeleted', true);
    query.ascending("name");
    query.limit(999);
    return query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
}
async function getGridKindsByCategory(category,cabinetId){
    console.log(category,cabinetId)
    const query = new Parse.Query(KindObj);
    query.notEqualTo('isDeleted', true);
    query.equalTo('category', category);
    query.ascending("name");
    const kinds=await query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
    const kindIds=kinds.map(k=>k.objectId);
    const query2 = new Parse.Query(CabinetGridObj);
    query2.equalTo('cabinetId', cabinetId.toString());
    query2.containedIn('kindId', kindIds);
    query2.ascending("number");
    const grids=await query2.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
    const gridDict=_.keyBy(grids,'kindId');
    return kinds.map(k=>{
        return{
            ...k,
            grid:gridDict[k.objectId]||[]
        }
    })
}

async function changeListStatus(listId, list){
    const parseList = BorrowListObj.createWithoutData(listId);
    parseList.set('list', list);
    parseList.set('hasUsed', true);
    parseList.set('apply_time', new Date());
    return parseList.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON);
}

async function save_record(kindId,goodId,user,count,isReturn) {
    if(!isReturn){
        const KouDaiRecord=new RecordObj();
        KouDaiRecord.set('kind',kindId);
        KouDaiRecord.set('good',goodId);
        KouDaiRecord.set('count',count||1);
        KouDaiRecord.set('user',user);
        KouDaiRecord.set('borrow_time',new Date());
        return KouDaiRecord.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON)
    }
    else {
        const query = new Parse.Query(RecordObj);
        query.equalTo("kind",kindId);
        if(goodId){
            query.equalTo("good",goodId);
        }
        // query.equalTo("user",user);
        query.notEqualTo("isReturn",true);
        const KouDaiRecord=await query.first({ useMasterKey: true });
        if(!KouDaiRecord){
            return
        }
        KouDaiRecord.set("isReturn",true)
        KouDaiRecord.set("return_time",new Date())
        return KouDaiRecord.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON)
    }
}
async function getRecordById(goodId){
    const query = new Parse.Query(RecordObj);
    query.equalTo("good",goodId);
    query.notEqualTo("isReturn",true);
    const record= await query.first({ useMasterKey: true }).then(ParseHelper.toJSON);
    const username=record.user;
    const _user=await UserService.getUserInfo(username);
    const user=ParseHelper.toJSON(_user);
    return {...record,user}
}


async function getSupplies() {
    const garidQuery = new Parse.Query(GridObj);
    garidQuery.greaterThan("count", 0);
    garidQuery.exists("kindId");
    garidQuery.notEqualTo("isDeleted", true);
    const garidResults = await garidQuery.find({ useMasterKey: true });
    const validKindIds = garidResults.map(item => item.get("kindId")); // 获取所有有效 kindId
    const query = new Parse.Query(KindObj);
    query.equalTo("hasCode", false);
    query.notEqualTo("isDeleted", true);
    query.containedIn("objectId", validKindIds); // 只包含在 validKindIds 中的 kindId
    query.limit(9999);
    return query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
}

async function checkSuppliesNumber(kindId) {
    try {
        const gridQuery = new Parse.Query(GridObj);
        gridQuery.equalTo("kindId", kindId);  // 查找指定kindId的物品
        gridQuery.greaterThan("count", 0);    // 只获取库存大于0的记录
        gridQuery.notEqualTo("isDeleted", true); // 排除已删除的记录
        const results = await gridQuery.find();  // 获取所有符合条件的记录
        console.log("results", results);
        // 计算count的总和
        const total = results.reduce((sum, record) => {
            return sum + (record.get("count") || 0);
        }, 0);
        console.log("total", total);
        return total;
    } catch (error) {
        console.error("库存检查失败:", error);
        throw new Error("库存检查失败");
    }
}

async function getReservationById(id) {
    const query = new Parse.Query(BorrowListObj);
    query.equalTo('isApply', true);
    query.notEqualTo('hasUsed', true);
    query.equalTo('objectId', id);
    const record=await query.first({ useMasterKey: true }).then(ParseHelper.toJSON);
    if(!record){
        return null;
    }
    const username=record.user;
    const _user=await UserService.getUserInfo(username);
    const user=ParseHelper.toJSON(_user);
    return {
        ...record,
        user
    };
}

async function validateReturnCode(id) {
    const query = new Parse.Query(BorrowListObj);
    query.equalTo('isApply', true);
    query.equalTo('hasUsed', true);
    query.equalTo('objectId', id);
    query.notEqualTo('hasReturn', true);
     const record=await query.first({ useMasterKey: true }).then(ParseHelper.toJSON);
    if(!record){
        return null;
    }
    const username=record.user;
    const _user=await UserService.getUserInfo(username);
    const user=ParseHelper.toJSON(_user);
    return {
        ...record,
        user
    };
}

async function validateFaultCode(id) {
    const query = new Parse.Query(FaultRecordObj);
    query.equalTo('objectId', id);
    const record = await query.first({ useMasterKey: true }).then(ParseHelper.toJSON);
    if (!record) {
        return null;
    }
    const borrowList=await getBorrowListById(record.listId);
    if(!borrowList){
        return null;
    }
    const username = record.username;
    const _user = await UserService.getUserInfo(username);
    const user = ParseHelper.toJSON(_user);
    return {
        ...record,
        user,
        list:borrowList.list
    };
}

async function getCabinetById(cabinetId) {
    const query = new Parse.Query(CabinetObj);
    query.equalTo("HID", cabinetId.toString());
    return query.first({ useMasterKey: true }).then(ParseHelper.toJSON);
}

async function checkGridAvailability(kindIds, cabinetId) {
    const cabinet = await getCabinetById(cabinetId);
    const query = new Parse.Query(GridObj);
    query.containedIn("kindId", kindIds);
    query.equalTo("cabinetId", cabinet.objectId);
    query.greaterThan("count", 0);
    query.notEqualTo("isDeleted", true);
    const grids = await query.find({ useMasterKey: true }).then(ParseHelper.toJSONList);
    //选出存放kindId最多的格子
    const gridsByKindId = _.groupBy(grids, 'kindId');
    const _grids = Object.keys(gridsByKindId).map(key => {
        const gridList = gridsByKindId[key];
        const maxGrid = gridList.reduce((max, current) => {
            return (current.count || 0) > (max.count || 0) ? current : max;
        }, gridList[0]);
        return {
            ...maxGrid,     
            kindId: key,
            count: maxGrid.count || 0
        };
    });
   
    
    return _grids;
}

async function changeGridStore(gridId,count) {
    const query = new Parse.Query(GridObj);
    query.notEqualTo("isDeleted", true);
    const grid = await query.get(gridId,{useMasterKey: true });
    const currentCount = grid.get("count") || 0;
    console.log("currentCount", currentCount,count);
    if (currentCount === Number(count)) {
        grid.set("count", 0);
        grid.set("kindId", null);
        grid.set("status", 'empty');
    }else{
        grid.decrement('count', Number(count));
    }
    grid.set("isFaultItem", null);
    return grid.save(null,{useMasterKey: true });
    
}

async function completeReservation(id) {
    const borrowList=await getBorrowListById(id);
    const list=borrowList.list||[];
    const newList=list.map(item=>{
        return {
            ...item,
            status:"used"
        }
    })
    const parseList = BorrowListObj.createWithoutData(id);
    parseList.set('hasUsed', true);
    parseList.set('apply_time',moment().format('YYYY-MM-DD HH:mm:ss'));
    parseList.set('list', newList);
    return parseList.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON);
}

async function addImageToBorrowList(listId, imageUrl) {
    const parseList = BorrowListObj.createWithoutData(listId);
    parseList.set('image', imageUrl);
    return parseList.save(null, { useMasterKey: true }).then(ParseHelper.toJSON);
}
async function addImageToFaultRecord(listId, imageUrl) {
    const FaultRecord = FaultRecordObj.createWithoutData(listId);
    FaultRecord.set('image', imageUrl);
    return FaultRecord.save(null, { useMasterKey: true }).then(ParseHelper.toJSON);
    
}


async function completeReturn(listId) {
    const borrowList=await getBorrowListById(listId);
    if(borrowList.hasReturn){
        return borrowList;
    }
    const list=borrowList.list||[];
    const newList=list.map(item=>{
        return {
            ...item,
            status:"returned"
        }
    })
    const parseList = BorrowListObj.createWithoutData(listId);
    parseList.set('hasReturn', true);
    parseList.set('return_time',moment().format('YYYY-MM-DD HH:mm:ss'));
    parseList.set('list', newList);
    return parseList.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON);
}

async function changeBorrowListStatus(id,status){
    const borrowList=await getBorrowListById(id);
    const list=borrowList.list||[];
    const newList=list.map(item=>{
        return {
            ...item,
            status
        }
    })
    const parseList = BorrowListObj.createWithoutData(id);
    parseList.set('hasUsed', false);
    parseList.set('list', newList);
    return parseList.save(null,{ useMasterKey: true }).then(ParseHelper.toJSON);
}

async function completeFault(id,cabinetId,grid,boardid) {
    const fault = FaultRecordObj.createWithoutData(id);
    fault.set('cabinetId', cabinetId);
    fault.set('grid', grid);
    fault.set('boardid', boardid);
    fault.set("status", "已放置");
    await fault.save(null, { useMasterKey: true });
    return fault;
}

async function getFaultRecordById(id) {
    const query = new Parse.Query(FaultRecordObj);
    return query.get(id, { useMasterKey: true }).then(ParseHelper.toJSON);
}

async function updateStatus(id,status,remark){
            const parseBorrowList = BorrowListObj.createWithoutData(id);
            // 根据状态设置相应的字段
            if (status === 'approved') {
                parseBorrowList.set('isApply', true);
            } else if (status === 'rejected') {
                parseBorrowList.set('isApply', false);
            } else if (status === 'returned') {
                parseBorrowList.set('hasReturn', true);
            }
            if (remark) {
                parseBorrowList.set('remark', remark);
            }
            return await parseBorrowList.save().then(result => result.toJSON());
}


module.exports={
    checkExcelFile,
    importExcelFile,
    _importKindData,
    update,
    add,
    remove,
    save_borrow_list,
    change_used_count,
    change_kind_used_count,
    save_list,
    change_status,
    getKindsByCategory,
    addToList,
    getUserBorrowList,
    getKindsByIds,
    getBorrowList,
    getUsersReturnedBorrowList,
    getKindById,
    getApplyCount,
    users_borrow_list,
    getBorrowListById,
    addFaultRecord,
    getFaultRecords,
    getAllFaultRecords,
    update_fault_record,
    complete_fault,
    save_koudai_record,
    getUsersApplyCount,
    getWhiteListBorrowList,
    download_box_records,
    download_records,
    download_repair_records,
    getCategorys,
    getGridKindsByCategory,
    changeListStatus,
    save_record,
    getRecordById,
    add_category,
    remove_category,
    change_store_count,
    exportExcel,
    getSupplies,
    checkSuppliesNumber,
    getWechatKindsByCategory,
    getCategoryAndKinds,
    getReservationById,
    checkGridAvailability,
    changeGridStore,
    completeReservation,
    validateReturnCode,
    validateFaultCode,
    addImageToBorrowList,
    completeReturn,
    completeFault,
    updateStatus,
    addImageToFaultRecord,
    changeBorrowListStatus,
    getFaultRecordById
};